<?php
/**
 * PHPExcel
 *
 * Copyright (c) 2006 - 2011 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
 *
 * @category    PHPExcel
 * @package        PHPExcel_Calculation
 * @copyright    Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license        http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
 * @version        1.7.6, 2011-02-27
 */


/** PHPExcel root directory */
if (!defined('PHPEXCEL_ROOT')) {
    /**
     * @ignore
     */
    define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
    require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
}


/** MAX_VALUE */
define('MAX_VALUE', 1.2e308);

/** 2 / PI */
define('M_2DIVPI', 0.63661977236758134307553505349006);

/** MAX_ITERATIONS */
define('MAX_ITERATIONS', 256);

/** PRECISION */
define('PRECISION', 8.88E-016);


/**
 * PHPExcel_Calculation_Functions
 *
 * @category    PHPExcel
 * @package        PHPExcel_Calculation
 * @copyright    Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
 */
class PHPExcel_Calculation_Functions {

    /** constants */
    const COMPATIBILITY_EXCEL        = 'Excel';
    const COMPATIBILITY_GNUMERIC    = 'Gnumeric';
    const COMPATIBILITY_OPENOFFICE    = 'OpenOfficeCalc';

    const RETURNDATE_PHP_NUMERIC    = 'P';
    const RETURNDATE_PHP_OBJECT        = 'O';
    const RETURNDATE_EXCEL            = 'E';


    /**
     *    Compatibility mode to use for error checking and responses
     *
     *    @access    private
     *    @var string
     */
    protected static $compatibilityMode    = self::COMPATIBILITY_EXCEL;

    /**
     *    Data Type to use when returning date values
     *
     *    @access    private
     *    @var string
     */
    protected static $ReturnDateType    = self::RETURNDATE_EXCEL;

    /**
     *    List of error codes
     *
     *    @access    private
     *    @var array
     */
    protected static $_errorCodes    = array( 'null'                => '#NULL!',
                                             'divisionbyzero'    => '#DIV/0!',
                                             'value'            => '#VALUE!',
                                             'reference'        => '#REF!',
                                             'name'                => '#NAME?',
                                             'num'                => '#NUM!',
                                             'na'                => '#N/A',
                                             'gettingdata'        => '#GETTING_DATA'
                                           );


    /**
     *    Set the Compatibility Mode
     *
     *    @access    public
     *    @category Function Configuration
     *    @param     string        $compatibilityMode        Compatibility Mode
     *                                                Permitted values are:
     *                                                    PHPExcel_Calculation_Functions::COMPATIBILITY_EXCEL            'Excel'
     *                                                    PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC        'Gnumeric'
     *                                                    PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE    'OpenOfficeCalc'
     *    @return     boolean    (Success or Failure)
     */
    public static function setCompatibilityMode($compatibilityMode) {
        if (($compatibilityMode == self::COMPATIBILITY_EXCEL) ||
            ($compatibilityMode == self::COMPATIBILITY_GNUMERIC) ||
            ($compatibilityMode == self::COMPATIBILITY_OPENOFFICE)) {
            self::$compatibilityMode = $compatibilityMode;
            return True;
        }
        return False;
    }    //    function setCompatibilityMode()


    /**
     *    Return the current Compatibility Mode
     *
     *    @access    public
     *    @category Function Configuration
     *    @return     string        Compatibility Mode
     *                            Possible Return values are:
     *                                PHPExcel_Calculation_Functions::COMPATIBILITY_EXCEL            'Excel'
     *                                PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC        'Gnumeric'
     *                                PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE    'OpenOfficeCalc'
     */
    public static function getCompatibilityMode() {
        return self::$compatibilityMode;
    }    //    function getCompatibilityMode()


    /**
     *    Set the Return Date Format used by functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object)
     *
     *    @access    public
     *    @category Function Configuration
     *    @param     string    $returnDateType            Return Date Format
     *                                                Permitted values are:
     *                                                    PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC        'P'
     *                                                    PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT        'O'
     *                                                    PHPExcel_Calculation_Functions::RETURNDATE_EXCEL            'E'
     *    @return     boolean                            Success or failure
     */
    public static function setReturnDateType($returnDateType) {
        if (($returnDateType == self::RETURNDATE_PHP_NUMERIC) ||
            ($returnDateType == self::RETURNDATE_PHP_OBJECT) ||
            ($returnDateType == self::RETURNDATE_EXCEL)) {
            self::$ReturnDateType = $returnDateType;
            return True;
        }
        return False;
    }    //    function setReturnDateType()


    /**
     *    Return the current Return Date Format for functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object)
     *
     *    @access    public
     *    @category Function Configuration
     *    @return     string        Return Date Format
     *                            Possible Return values are:
     *                                PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC        'P'
     *                                PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT        'O'
     *                                PHPExcel_Calculation_Functions::RETURNDATE_EXCEL            'E'
     */
    public static function getReturnDateType() {
        return self::$ReturnDateType;
    }    //    function getReturnDateType()


    /**
     *    DUMMY
     *
     *    @access    public
     *    @category Error Returns
     *    @return    string    #Not Yet Implemented
     */
    public static function DUMMY() {
        return '#Not Yet Implemented';
    }    //    function DUMMY()


    /**
     *    DIV0
     *
     *    @access    public
     *    @category Error Returns
     *    @return    string    #Not Yet Implemented
     */
    public static function DIV0() {
        return self::$_errorCodes['divisionbyzero'];
    }    //    function DIV0()


    /**
     *    NA
     *
     *    Excel Function:
     *        =NA()
     *
     *    Returns the error value #N/A
     *        #N/A is the error value that means "no value is available."
     *
     *    @access    public
     *    @category Logical Functions
     *    @return    string    #N/A!
     */
    public static function NA() {
        return self::$_errorCodes['na'];
    }    //    function NA()


    /**
     *    NaN
     *
     *    Returns the error value #NUM!
     *
     *    @access    public
     *    @category Error Returns
     *    @return    string    #NUM!
     */
    public static function NaN() {
        return self::$_errorCodes['num'];
    }    //    function NaN()


    /**
     *    NAME
     *
     *    Returns the error value #NAME?
     *
     *    @access    public
     *    @category Error Returns
     *    @return    string    #NAME?
     */
    public static function NAME() {
        return self::$_errorCodes['name'];
    }    //    function NAME()


    /**
     *    REF
     *
     *    Returns the error value #REF!
     *
     *    @access    public
     *    @category Error Returns
     *    @return    string    #REF!
     */
    public static function REF() {
        return self::$_errorCodes['reference'];
    }    //    function REF()


    /**
     *    NULL
     *
     *    Returns the error value #NULL!
     *
     *    @access    public
     *    @category Error Returns
     *    @return    string    #REF!
     */
    public static function NULL() {
        return self::$_errorCodes['null'];
    }    //    function NULL()


    /**
     *    VALUE
     *
     *    Returns the error value #VALUE!
     *
     *    @access    public
     *    @category Error Returns
     *    @return    string    #VALUE!
     */
    public static function VALUE() {
        return self::$_errorCodes['value'];
    }    //    function VALUE()


    public static function isMatrixValue($idx) {
        return ((substr_count($idx,'.') <= 1) || (preg_match('/\.[A-Z]/',$idx) > 0));
    }


    public static function isValue($idx) {
        return (substr_count($idx,'.') == 0);
    }


    public static function isCellValue($idx) {
        return (substr_count($idx,'.') > 1);
    }


    public static function _ifCondition($condition) {
        $condition    = PHPExcel_Calculation_Functions::flattenSingleValue($condition);
        if (!in_array($condition{0},array('>', '<', '='))) {
            if (!is_numeric($condition)) { $condition = PHPExcel_Calculation::_wrapResult(strtoupper($condition)); }
            return '='.$condition;
        } else {
            preg_match('/([<>=]+)(.*)/',$condition,$matches);
            list(,$operator,$operand) = $matches;
            if (!is_numeric($operand)) { $operand = PHPExcel_Calculation::_wrapResult(strtoupper($operand)); }
            return $operator.$operand;
        }
    }    //    function _ifCondition()


    /**
     *    ERROR_TYPE
     *
     *    @param    mixed    $value    Value to check
     *    @return    boolean
     */
    public static function ERROR_TYPE($value = '') {
        $value    = self::flattenSingleValue($value);

        $i = 1;
        foreach(self::$_errorCodes as $errorCode) {
            if ($value == $errorCode) {
                return $i;
            }
            ++$i;
        }
        return self::$_errorCodes['na'];
    }    //    function ERROR_TYPE()


    /**
     *    IS_BLANK
     *
     *    @param    mixed    $value    Value to check
     *    @return    boolean
     */
    public static function IS_BLANK($value=null) {
        if (!is_null($value)) {
            $value    = self::flattenSingleValue($value);
        }

        return is_null($value);
    }    //    function IS_BLANK()


    /**
     *    IS_ERR
     *
     *    @param    mixed    $value    Value to check
     *    @return    boolean
     */
    public static function IS_ERR($value = '') {
        $value        = self::flattenSingleValue($value);

        return self::IS_ERROR($value) && (!self::IS_NA($value));
    }    //    function IS_ERR()


    /**
     *    IS_ERROR
     *
     *    @param    mixed    $value    Value to check
     *    @return    boolean
     */
    public static function IS_ERROR($value = '') {
        $value        = self::flattenSingleValue($value);

        return in_array($value, array_values(self::$_errorCodes));
    }    //    function IS_ERROR()


    /**
     *    IS_NA
     *
     *    @param    mixed    $value    Value to check
     *    @return    boolean
     */
    public static function IS_NA($value = '') {
        $value        = self::flattenSingleValue($value);

        return ($value === self::$_errorCodes['na']);
    }    //    function IS_NA()


    /**
     *    IS_EVEN
     *
     *    @param    mixed    $value    Value to check
     *    @return    boolean
     */
    public static function IS_EVEN($value = 0) {
        $value        = self::flattenSingleValue($value);

        if ((is_bool($value)) || ((is_string($value)) && (!is_numeric($value)))) {
            return self::$_errorCodes['value'];
        }
        return ($value % 2 == 0);
    }    //    function IS_EVEN()


    /**
     *    IS_ODD
     *
     *    @param    mixed    $value    Value to check
     *    @return    boolean
     */
    public static function IS_ODD($value = null) {
        $value    = self::flattenSingleValue($value);

        if ((is_bool($value)) || ((is_string($value)) && (!is_numeric($value)))) {
            return self::$_errorCodes['value'];
        }
        return (abs($value) % 2 == 1);
    }    //    function IS_ODD()


    /**
     *    IS_NUMBER
     *
     *    @param    mixed    $value        Value to check
     *    @return    boolean
     */
    public static function IS_NUMBER($value = 0) {
        $value        = self::flattenSingleValue($value);

        if (is_string($value)) {
            return False;
        }
        return is_numeric($value);
    }    //    function IS_NUMBER()


    /**
     *    IS_LOGICAL
     *
     *    @param    mixed    $value        Value to check
     *    @return    boolean
     */
    public static function IS_LOGICAL($value = true) {
        $value        = self::flattenSingleValue($value);

        return is_bool($value);
    }    //    function IS_LOGICAL()


    /**
     *    IS_TEXT
     *
     *    @param    mixed    $value        Value to check
     *    @return    boolean
     */
    public static function IS_TEXT($value = '') {
        $value        = self::flattenSingleValue($value);

        return is_string($value);
    }    //    function IS_TEXT()


    /**
     *    IS_NONTEXT
     *
     *    @param    mixed    $value        Value to check
     *    @return    boolean
     */
    public static function IS_NONTEXT($value = '') {
        return !self::IS_TEXT($value);
    }    //    function IS_NONTEXT()


    /**
     *    VERSION
     *
     *    @return    string    Version information
     */
    public static function VERSION() {
        return 'PHPExcel 1.7.6, 2011-02-27';
    }    //    function VERSION()


    /**
     *    N
     *
     *    Returns a value converted to a number
     *
     *    @param    value        The value you want converted
     *    @return    number        N converts values listed in the following table
     *        If value is or refers to N returns
     *        A number            That number
     *        A date                The serial number of that date
     *        TRUE                1
     *        FALSE                0
     *        An error value        The error value
     *        Anything else        0
     */
    public static function N($value) {
        while (is_array($value)) {
            $value = array_shift($value);
        }

        switch (gettype($value)) {
            case 'double'    :
            case 'float'    :
            case 'integer'    :
                return $value;
                break;
            case 'boolean'    :
                return (integer) $value;
                break;
            case 'string'    :
                //    Errors
                if ((strlen($value) > 0) && ($value{0} == '#')) {
                    return $value;
                }
                break;
        }
        return 0;
    }    //    function N()


    /**
     *    TYPE
     *
     *    Returns a number that identifies the type of a value
     *
     *    @param    value        The value you want tested
     *    @return    number        N converts values listed in the following table
     *        If value is or refers to N returns
     *        A number            1
     *        Text                2
     *        Logical Value        4
     *        An error value        16
     *        Array or Matrix        64
     */
    public static function TYPE($value) {
        $value    = self::flattenArrayIndexed($value);
        if (is_array($value) && (count($value) > 1)) {
            $a = array_keys($value);
            $a = array_pop($a);
            //    Range of cells is an error
            if (self::isCellValue($a)) {
                return 16;
            //    Test for Matrix
            } elseif (self::isMatrixValue($a)) {
                return 64;
            }
        } elseif(count($value) == 0) {
            //    Empty Cell
            return 1;
        }
        $value    = self::flattenSingleValue($value);

        if ((is_float($value)) || (is_int($value))) {
                return 1;
        } elseif(is_bool($value)) {
                return 4;
        } elseif(is_array($value)) {
                return 64;
                break;
        } elseif(is_string($value)) {
            //    Errors
            if ((strlen($value) > 0) && ($value{0} == '#')) {
                return 16;
            }
            return 2;
        }
        return 0;
    }    //    function TYPE()


    /**
     *    Convert a multi-dimensional array to a simple 1-dimensional array
     *
     *    @param    array    $array    Array to be flattened
     *    @return    array    Flattened array
     */
    public static function flattenArray($array) {
        if (!is_array($array)) {
            return (array) $array;
        }

        $arrayValues = array();
        foreach ($array as $value) {
            if (is_array($value)) {
                foreach ($value as $val) {
                    if (is_array($val)) {
                        foreach ($val as $v) {
                            $arrayValues[] = $v;
                        }
                    } else {
                        $arrayValues[] = $val;
                    }
                }
            } else {
                $arrayValues[] = $value;
            }
        }

        return $arrayValues;
    }    //    function flattenArray()


    /**
     *    Convert a multi-dimensional array to a simple 1-dimensional array, but retain an element of indexing
     *
     *    @param    array    $array    Array to be flattened
     *    @return    array    Flattened array
     */
    public static function flattenArrayIndexed($array) {
        if (!is_array($array)) {
            return (array) $array;
        }

        $arrayValues = array();
        foreach ($array as $k1 => $value) {
            if (is_array($value)) {
                foreach ($value as $k2 => $val) {
                    if (is_array($val)) {
                        foreach ($val as $k3 => $v) {
                            $arrayValues[$k1.'.'.$k2.'.'.$k3] = $v;
                        }
                    } else {
                        $arrayValues[$k1.'.'.$k2] = $val;
                    }
                }
            } else {
                $arrayValues[$k1] = $value;
            }
        }

        return $arrayValues;
    }    //    function flattenArrayIndexed()


    /**
     *    Convert an array to a single scalar value by extracting the first element
     *
     *    @param    mixed        $value        Array or scalar value
     *    @return    mixed
     */
    public static function flattenSingleValue($value = '') {
        while (is_array($value)) {
            $value = array_pop($value);
        }

        return $value;
    }    //    function flattenSingleValue()

}    //    class PHPExcel_Calculation_Functions


//
//    There are a few mathematical functions that aren't available on all versions of PHP for all platforms
//    These functions aren't available in Windows implementations of PHP prior to version 5.3.0
//    So we test if they do exist for this version of PHP/operating platform; and if not we create them
//
if (!function_exists('acosh')) {
    function acosh($x) {
        return 2 * log(sqrt(($x + 1) / 2) + sqrt(($x - 1) / 2));
    }    //    function acosh()
}

if (!function_exists('asinh')) {
    function asinh($x) {
        return log($x + sqrt(1 + $x * $x));
    }    //    function asinh()
}

if (!function_exists('atanh')) {
    function atanh($x) {
        return (log(1 + $x) - log(1 - $x)) / 2;
    }    //    function atanh()
}

if (!function_exists('money_format')) {
    function money_format($format, $number) {
        $regex = array( '/%((?:[\^!\-]|\+|\(|\=.)*)([0-9]+)?(?:#([0-9]+))?',
                         '(?:\.([0-9]+))?([in%])/'
                      );
        $regex = implode('', $regex);
        if (setlocale(LC_MONETARY, null) == '') {
            setlocale(LC_MONETARY, '');
        }
        $locale = localeconv();
        $number = floatval($number);
        if (!preg_match($regex, $format, $fmatch)) {
            trigger_error("No format specified or invalid format", E_USER_WARNING);
            return $number;
        }
        $flags = array( 'fillchar'    => preg_match('/\=(.)/', $fmatch[1], $match) ? $match[1] : ' ',
                        'nogroup'    => preg_match('/\^/', $fmatch[1]) > 0,
                        'usesignal'    => preg_match('/\+|\(/', $fmatch[1], $match) ? $match[0] : '+',
                        'nosimbol'    => preg_match('/\!/', $fmatch[1]) > 0,
                        'isleft'    => preg_match('/\-/', $fmatch[1]) > 0
                      );
        $width    = trim($fmatch[2]) ? (int)$fmatch[2] : 0;
        $left    = trim($fmatch[3]) ? (int)$fmatch[3] : 0;
        $right    = trim($fmatch[4]) ? (int)$fmatch[4] : $locale['int_frac_digits'];
        $conversion = $fmatch[5];
        $positive = true;
        if ($number < 0) {
            $positive = false;
            $number *= -1;
        }
        $letter = $positive ? 'p' : 'n';
        $prefix = $suffix = $cprefix = $csuffix = $signal = '';
        if (!$positive) {
            $signal = $locale['negative_sign'];
            switch (true) {
                case $locale['n_sign_posn'] == 0 || $flags['usesignal'] == '(':
                    $prefix = '(';
                    $suffix = ')';
                    break;
                case $locale['n_sign_posn'] == 1:
                    $prefix = $signal;
                    break;
                case $locale['n_sign_posn'] == 2:
                    $suffix = $signal;
                    break;
                case $locale['n_sign_posn'] == 3:
                    $cprefix = $signal;
                    break;
                case $locale['n_sign_posn'] == 4:
                    $csuffix = $signal;
                    break;
            }
        }
        if (!$flags['nosimbol']) {
            $currency = $cprefix;
            $currency .= ($conversion == 'i' ? $locale['int_curr_symbol'] : $locale['currency_symbol']);
            $currency .= $csuffix;
            $currency = iconv('ISO-8859-1','UTF-8',$currency);
        } else {
            $currency = '';
        }
        $space = $locale["{$letter}_sep_by_space"] ? ' ' : '';

        $number = number_format($number, $right, $locale['mon_decimal_point'], $flags['nogroup'] ? '' : $locale['mon_thousands_sep'] );
        $number = explode($locale['mon_decimal_point'], $number);

        $n = strlen($prefix) + strlen($currency);
        if ($left > 0 && $left > $n) {
            if ($flags['isleft']) {
                $number[0] .= str_repeat($flags['fillchar'], $left - $n);
            } else {
                $number[0] = str_repeat($flags['fillchar'], $left - $n) . $number[0];
            }
        }
        $number = implode($locale['mon_decimal_point'], $number);
        if ($locale["{$letter}_cs_precedes"]) {
            $number = $prefix . $currency . $space . $number . $suffix;
        } else {
            $number = $prefix . $number . $space . $currency . $suffix;
        }
        if ($width > 0) {
            $number = str_pad($number, $width, $flags['fillchar'], $flags['isleft'] ? STR_PAD_RIGHT : STR_PAD_LEFT);
        }
        $format = str_replace($fmatch[0], $number, $format);
        return $format;
    }    //    function money_format()
}


//
//    Strangely, PHP doesn't have a mb_str_replace multibyte function
//    As we'll only ever use this function with UTF-8 characters, we can simply "hard-code" the character set
//
if ((!function_exists('mb_str_replace')) &&
    (function_exists('mb_substr')) && (function_exists('mb_strlen')) && (function_exists('mb_strpos'))) {
    function mb_str_replace($search, $replace, $subject) {
        if(is_array($subject)) {
            $ret = array();
            foreach($subject as $key => $val) {
                $ret[$key] = mb_str_replace($search, $replace, $val);
            }
            return $ret;
        }

        foreach((array) $search as $key => $s) {
            if($s == '') {
                continue;
            }
            $r = !is_array($replace) ? $replace : (array_key_exists($key, $replace) ? $replace[$key] : '');
            $pos = mb_strpos($subject, $s, 0, 'UTF-8');
            while($pos !== false) {
                $subject = mb_substr($subject, 0, $pos, 'UTF-8') . $r . mb_substr($subject, $pos + mb_strlen($s, 'UTF-8'), 65535, 'UTF-8');
                $pos = mb_strpos($subject, $s, $pos + mb_strlen($r, 'UTF-8'), 'UTF-8');
            }
        }
        return $subject;
    }
}
